
******************** TIM AND CUSTOMS DESCRIPTIVE STATISTICS

*  NOTE: BEFORE RUNNING THIS CODE RUN restat_submission_codes_match.do

clear all
cd 

******************************************************************************

capture log close
log using descriptives, replace text

************* Table 1

use customs_tim_trans_data.dta, clear
	
* Calculations
bys sem_year: egen n_exp_value = total(exp_value)
bys sem_year: egen nt_exp_value = total(exp_value*tim_use)
gen st_exp_value = nt_exp_value/n_exp_value

bys sem_year: gen n_shipms = _N
bys sem_year: egen nt_shipms = total(tim_use)
gen st_shipms = nt_shipms/n_shipms

foreach v in id_firm product importer id_buyer {
	* Total
	bys sem_year `v': gen i_`v' = _n==1
	bys sem_year: egen n_`v' = total(i_`v')
	* TIM use
	bys sem_year `v': egen tim_`v' = max(tim_use)
	bys sem_year: egen nt_`v' = total(i_`v'*tim_`v' )
	* Share
	gen st_`v' = nt_`v'/n_`v'
}

keep sem_year year semester n_* st_*
duplicates drop

matrix table1 = J(16,8,.)

local i = 1 
forvalues y = 2010/2013 {
	forvalues s = 1/2 {		
		
		matrix table1[`i',1] = `y'
		matrix table1[`i',2] = `s'	
		
		local j = 2
		foreach v in exp_value shipms id_firm product importer id_buyer {
				local j = `j' + 1
				local k = `i' + 1
				sum n_`v' if year == `y'&semester==`s'
				matrix table1[`i',`j'] = r(mean)
				sum st_`v' if year == `y'&semester==`s'
				matrix table1[`k',`j'] = r(mean)		
		}		
		local i = `i' + 2
	}
}	

clear 
svmat table1
export delimited using table1, nolabel replace

************* Figure 3

* Municipality with positive exports *

use customs_tim_trans_data.dta, clear
keep if tim_use == 1
keep munic
duplicates drop
destring munic, replace
save count_aux.dta, replace

* Fiscal Routes *
use tim_aux_route.dta, clear

destring custom_int, gen(custom_code)
joinby custom_code using customs_municip_codes.dta, unm(m)
keep country_end custom date_day cod_mun
duplicates drop
drop if date_day==.

* Distance between internal custom and municipalities less than 50 km
destring cod_mun, gen(mun_cus)
joinby mun_cus using mun_ij_distances_long.dta, unm(m)
rename mun_firm munic

keep if dist<50 /*distance variable in kms*/

* Level of fiscal routes
gen sem_year = hofd(date_day)
format sem_year %th 
keep sem_year munic custom country_end
duplicates drop

* Count relevant routes
merge m:1 munic using count_aux.dta, nogen keep(3)

bys sem_year: gen tot_fis_routes_dist50 = _N

keep sem_year tot_fis_routes_dist50
duplicates drop

save fiscal_aux.dta, replace

* Export Routes *

use customs_tim_trans_data.dta, clear
keep if tim_use == 1
keep if importer=="CR"|importer=="GT"|importer=="HN"|importer=="NI"|importer=="PA"

keep sem_year importer custom munic
duplicates drop

bys sem_year: gen nra_cus_ca = _N
keep sem_year nra_cus_ca
duplicates drop

merge 1:1 sem_year using fiscal_aux.dta, nogen

* Figure *

twoway line nra_cus_ca sem_year, lc(black) lp(solid) || line  tot_fis_routes_dist50 sem_year, lc(gray) lp(dash) ///
yti("Number of Routes", axis(1) margin(medsmall)) ///
tlabel(2010h1 "2010 1st sem" 2011h1 "2011 1st sem" 2012h1 "2012 1st sem" 2013h1 "2013 1st sem", labs(medsmall) angle(315)) graphregion(color(white)) xsc(r(100 107)) ///
legend(lab(1 "Export Routes") lab(2 "Fiscal Routes") col(3) size(medsmall)) tti("") 			

graph export figure3.pdf, replace

************* Table A1

matrix tableA1 = J(18,8,.)

local i = 0
foreach level in munic id_firm {

	use customs_tim_trans_data.dta, clear

	bys `level' importer custom sem_year: egen exp_value_m = total(exp_value), missing
	keep `level' importer custom sem_year year semester exp_value_m 
	duplicates drop

	* Semester-specific
	forvalues y = 2010/2013 {
		forvalues s = 1/2 {
			local i = `i' + 1
			
			matrix tableA1[`i',1] = `y'
			matrix tableA1[`i',2] = `s'
			
			sum exp_value_m if year == `y'&semester==`s', det
			
			local j = 2
			foreach a in mean sd p25 p50 p75 N {
				local j = `j' + 1
				matrix tableA1[`i',`j'] = r(`a') 
			}
			
		}
	}
	
	* Full semester
	local i = `i' + 1
	
	sum exp_value_m, det
	
	local j = 2
	foreach a in mean sd p25 p50 p75 N {
		local j = `j' + 1
		matrix tableA1[`i',`j'] = r(`a') 
	}		
}

clear 
svmat tableA1
export delimited * using tableA1, nolabel replace

************* Figure A1

* Totals matched in customs data
use customs_tim_trans_data.dta, clear
bys sem_year: egen exp_cus = total(exp_value*tim_use)
keep sem_year exp_cus
duplicates drop
save a1_cus.dta, replace

* Totals in tims data 
use tim_base_for_matching.dta, clear
gen sem_year = hofd(date_day)
bys sem_year: egen exp_tim = total(trans_value)
keep sem_year exp_tim
duplicates drop
save a1_tim.dta, replace

* Figure 
use a1_tim.dta, clear
merge 1:1 sem_year using a1_cus.dta, gen(_m)

sort sem_year
tsset sem_year, halfyearly 


twoway line exp_tim sem_year, lc(black) lp(solid) ///
	   tlabel(2010h1 "2010 1st sem" 2011h1 "2011 1st sem" 2012h1 "2012 1st sem" 2013h1 "2013 1st sem", labs(medsmall) angle(315)) || ///	   
	   line exp_cus sem_year, lc(gray) lp(dash)  tti("") yti("Value (million USD)", margin(medsmall)) ///
	   legend(lab(1 "Transit Value (DUTs)") lab(2 "Exports Value (DUAs)")) graphregion(color(white))  

graph export figureA1.pdf, replace

log close
clear
